﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using SharpMap.Data;
using SharpMap.Data.Providers;
using SharpMap.Layers;
using SharpMap.Geometries;
using MsSpatialTools;
using System.Drawing;
using SharpMap.Forms;
using System.Collections.ObjectModel;
namespace QueryVisualizerTools
{
    /// <summary>
    /// 简单查询 select * from tableName where 
    /// </summary>
    public class MsSqlHelper
    {
        //private string _SqlServer = "";
        private string _FeatureColumns = "";
        private string geoColumn = "geometry";
        public MsSqlHelper(string dateBase)
        {
            //this._SqlServer = sqlServer;
            DBHelper.Database = dateBase;
        }
        #region 使用MsSqlspatial 数据引擎 方式
        public LayerCollection getLayerCollection(string mysql)
        {
            LayerCollection lc = new LayerCollection();
            string[] sqls = mysql.Split(';');
            foreach (string s in sqls)
            {
                VectorLayer vl = getVLayer(s);
                lc.Add(vl);
            }
            return lc;
        }
        private VectorLayer getVLayer(string mysql)
        {
            #region 查询语句分解
            mysql = mysql.ToLower().Trim();
            int s_index ,length=0;
            string s_flag , e_flag;

            s_flag = "select";
            e_flag = "from";
            s_index = mysql.LastIndexOf(s_flag) + s_flag.Length;
            length = mysql.LastIndexOf(e_flag) - s_index;
            string featureColumns = mysql.Substring(s_index, length).Trim();

            s_flag = "from";
            e_flag = "where";
            string whereClause = "1=1";
            string tbName = "";
            if (mysql.Contains(e_flag))
            {
                s_index = mysql.LastIndexOf(s_flag) + s_flag.Length;
                length = mysql.LastIndexOf(e_flag) - s_index;
                tbName = mysql.Substring(s_index, length).Trim();

                s_flag = "where";
                s_index = mysql.LastIndexOf(s_flag) + s_flag.Length;
                whereClause = mysql.Substring(s_index).Trim();
            }
            else
            {
                s_index = mysql.LastIndexOf(s_flag) + s_flag.Length;              
                tbName = mysql.Substring(s_index).Trim();
            }
            #endregion
            VectorLayer vl = new VectorLayer(tbName);
            MsSqlSpatial mss = new MsSqlSpatial(
                DBHelper.getConnectionString, tbName, "oid");
            mss.DefinitionQuery = whereClause;
            mss.FeatureColumns = featureColumns;
            vl.DataSource = mss;
            vl.SRID = 4326;
            return vl;
        }
        private void renderLayer(VectorLayer vl)
        {          
            vl.Style.Fill = new SolidBrush(this.getRandomColor());
            //Set the polygons to have a black outline
            vl.Style.Outline = System.Drawing.Pens.Black;
            vl.Style.EnableOutline = true;
        }
        private Color getRandomColor()
        {
            int seed = int.Parse(System.DateTime.Now.ToString("hhMMss"));
            Random rand = new Random(seed);
            Color color = Color.FromArgb(rand.Next(255), rand.Next(255), rand.Next(255));
            return color;
        }
        public void addToMapControl(LayerCollection lc, MapImage mc)
        {
            mc.Map.Layers.Clear();
            if (mc.Map.Layers.Count > 0)
            {
                foreach (VectorLayer vl in lc)
                {
                    mc.Map.Layers.Add(vl);
                }

                mc.Map.MaximumZoom = 360;
                //map.BackColor = Color.LightBlue;
                mc.Map.Zoom = 360;
                mc.Map.Center = new SharpMap.Geometries.Point(0, 0);

                mc.Map.ZoomToExtents();  
            }
            mc.Refresh();
        }
        #endregion

        #region 使用sql server引擎，然后编程生成 地理图形
        public DataSet getDataSet(string mysql)
        {
            DataSet ds = new DataSet();
            mysql = mysql.Replace("\n", " ");
            string[] sqls = mysql.Split(';');
            foreach (string s in sqls)
            {
                if (s.Trim() != "")
                {
                    ds.Tables.Add(DBHelper.getDataTable(mysql));
                }
            }
            return ds;
        }
        public void addToMapControl(DataSet ds,MapImage mc)
        {
            //列 tmp_geometry 为空间列，其他不是
            //SELECT *, 'Blue' as LineColor, 3 as LineThickness FROM Rivers
            //'Green' AS FillColor,
            
            LayerCollection lc = new LayerCollection();
            //GeometryProvider
            // GeometryProvider gp=new GeometryProvider (
            for (int i=0; i < ds.Tables.Count;i++ )
            {
                DataTable dt=ds.Tables[i];
                if (dt.Columns.Contains(geoColumn))
                {
                    lc.Add(this.generateLayer(dt));
                    dt.Columns.Remove(geoColumn);
                }
            }
            mc.Map.Layers = lc;
            if (mc.Map.Layers.Count > 0)
            {
                mc.Map.ZoomToExtents();            
            }
            mc.Refresh();
        }
        private Layer generateLayer(DataTable dt)
        {
            //if (dt.Rows.Count < 1)
            //    return;
            Collection<Geometry> cgeos = new Collection<Geometry>();
            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    Geometry geo = SharpMap.Converters.WellKnownText.GeometryFromWKT.Parse(
                        dr[this.geoColumn].ToString());
                    cgeos.Add(geo);
                }
                catch { }
            }
            VectorLayer vlyr = new VectorLayer(dt.TableName);
            vlyr.DataSource = new GeometryProvider(cgeos);
            this.renderLayer(vlyr);
            return vlyr;
        }
        
        #endregion
    }
}
